﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using VCN.App.DataTransferObjects;
using System.Data.SqlClient;

namespace VCN.App.DataAccessLayer
{
    public class ProductDAO : BasicDAO<Product>
    {
        protected override DataTable exeSpSelect(string ip_storedProcedure)
        {
            return base.exeSpSelect("sp_tblProducts_SelectAll");//sp_tblCategories_SelectAll
        }
        public DataTable selectAll2Dt()
        {
            return this.exeSpSelect("");
        }
        public DataTable searchProduct(int ip_IdSP,string ip_TenSP)
        {
            SqlConnection v_conn = BasicDAO<Product>.GetConnection();
            if (v_conn.State == ConnectionState.Closed)
            {
                v_conn.Open();
            }
            SqlCommand v_command = new SqlCommand("sp_tblProducts_Search", v_conn);
            v_command.CommandType = CommandType.StoredProcedure;
            v_command.Parameters.Add("@Id", SqlDbType.Int).Value = ip_IdSP;
            v_command.Parameters.Add("@Name", SqlDbType.NVarChar).Value = ip_TenSP;
            DataTable v_dt = new DataTable();
            SqlDataAdapter adapter = new SqlDataAdapter(v_command);
            try
            {

                adapter.Fill(v_dt);
                v_conn.Close();
            }
            catch (Exception exp)
            {
                v_conn.Close();
                HttpContext.Current.Response.Write(exp.ToString());
            }
            return v_dt;
        }
        protected  DataTable exeSpSelectById(string ip_storeProcedure, int ip_id)
        {
            return base.exeSpSelectById("sp_tblProducts_SelectRow", ip_id);
        }
        public Product SelectById(int ip_id)
        {
            DataTable v_dt = this.exeSpSelectById("", ip_id);
            Product v_Product = new Product();
            v_Product.Id = ip_id;
            v_Product.Name = v_dt.Rows[0]["PRODUCT_NAME"].ToString();
            v_Product.Description = v_dt.Rows[0]["PRODUCT_DESCRIPTION"].ToString();
            v_Product.Image = v_dt.Rows[0]["PRODUCT_IMAGE"].ToString();
            v_Product.CateId = int.Parse(v_dt.Rows[0]["CATEGORY_ID"].ToString());
            v_Product.ProductKey = v_dt.Rows[0]["PRODUCT_KEY"].ToString();
            return v_Product;
        }

        public override bool insert(Product ip_product)
        {
            SqlConnection v_conn = BasicDAO<Product>.GetConnection();
            if (v_conn.State == ConnectionState.Closed)
            {
                v_conn.Open();
            }
            SqlCommand v_command = new SqlCommand("sp_tblProducts_Insert", v_conn);
            v_command.CommandType = CommandType.StoredProcedure;
            v_command.Parameters.Add("@Name", SqlDbType.NVarChar).Value = ip_product.Name;
            v_command.Parameters.Add("@Description", SqlDbType.NVarChar).Value = ip_product.Description;
            v_command.Parameters.Add("@Image", SqlDbType.VarChar).Value = ip_product.Image;
            v_command.Parameters.Add("@CateId", SqlDbType.Int).Value = ip_product.CateId;
            v_command.Parameters.Add("@ProductKey", SqlDbType.NVarChar).Value = ip_product.ProductKey;
            try
            {
                int v_rows = v_command.ExecuteNonQuery();
                v_conn.Close();
            }
            catch (Exception exp)
            {
                v_conn.Close();
                HttpContext.Current.Response.Write(exp.ToString());
                return false;
            }
            return true;


        }
        //public override bool deleteByID(Product ip_product)
        //{
        //    return base.deleteByID(ip_product);
        //}
        public override System.Collections.Generic.List<Product> selectAll2List(Product ip_obj)
        {
            return null;
        }

        public override bool update(Product ip_product)
        {
            SqlConnection v_conn = BasicDAO<Product>.GetConnection();
            if (v_conn.State == ConnectionState.Closed)
            {
                v_conn.Open();
            }
            SqlCommand v_command = new SqlCommand("sp_tblProducts_Update", v_conn);
            v_command.CommandType = CommandType.StoredProcedure;
            v_command.Parameters.Add("@Id", SqlDbType.Int).Value = ip_product.Id;
            v_command.Parameters.Add("@Name", SqlDbType.NVarChar).Value = ip_product.Name;
            v_command.Parameters.Add("@Description", SqlDbType.NVarChar).Value = ip_product.Description;
            v_command.Parameters.Add("@Image", SqlDbType.VarChar).Value = ip_product.Image;
            v_command.Parameters.Add("@CateId", SqlDbType.Int).Value = ip_product.CateId;
            v_command.Parameters.Add("@ProductKey", SqlDbType.NVarChar).Value = ip_product.ProductKey;
            try
            {
                int v_rows = v_command.ExecuteNonQuery();
                v_conn.Close();
            }
            catch (Exception exp)
            {
                v_conn.Close();
                HttpContext.Current.Response.Write(exp.ToString());
                return false;
            }
            return true;
        }
        public bool delete(Product ip_product)
        {
            SqlConnection v_conn = BasicDAO<Product>.GetConnection();
            if (v_conn.State == ConnectionState.Closed)
            {
                v_conn.Open();
            }
            SqlCommand v_command = new SqlCommand("sp_tblProducts_DeleteRow", v_conn);
            v_command.CommandType = CommandType.StoredProcedure;
            v_command.Parameters.Add("@Id", SqlDbType.Int).Value = ip_product.Id;
            try
            {
                int v_rows = v_command.ExecuteNonQuery();
                v_conn.Close();
            }
            catch (Exception exp)
            {
                v_conn.Close();
               // HttpContext.Current.Response.Write(exp.ToString());
                return false;
            }
            return true;
        }
        //protected override DataTable exeSpSelect()
        //{
        //    return base.exeSpSelect("sp_tblProducts_select");
        //}
        public DataTable SelectByCategoryId(int ip_CategoryId)
        {
            SqlConnection v_conn = BasicDAO<Product>.GetConnection();
            if (v_conn.State == ConnectionState.Closed)
            {
                v_conn.Open();
            }
            SqlCommand v_command = new SqlCommand("sp_tblProducts_SelectCategoryId", v_conn);
            v_command.CommandType = CommandType.StoredProcedure;
            v_command.Parameters.Add("@CategoryId", SqlDbType.Int).Value = ip_CategoryId;
            DataTable v_dt = new DataTable();
            SqlDataAdapter v_adap = new SqlDataAdapter(v_command);
            try
            {
                v_adap.Fill(v_dt);
                v_conn.Close();
            }
            catch (Exception exp)
            {
                v_conn.Close();
                HttpContext.Current.Response.Write(exp.ToString());

            }
            return v_dt;


        }
    }
}
